#!/usr/bin/python
# encoding=utf-8
'''
pip install PyMySQL

https://pymysql.readthedocs.io/en/latest/
'''
import time
import pymysql

connection = pymysql.connect(host='localhost',
                             user='root',
                             password='abcd4321',
                             db='my-all-in',
                             charset='utf8mb4',
                             autocommit=True)


def new_register(nickname, cursor):
    print(nickname, 'new register task')
    return cursor.execute(
        '''
            insert into t_job_task(type, biz, status, done)
            values('YBC_NEW_REGISTER', %s, 'NEW', 0)
        ''', ('{"promotionCode":"' + nickname + '"}'))

try:
    with connection.cursor() as cursor:
        while True:
            cursor.execute('''
                select count(1) from t_job_task where type = 'YBC_NEW_REGISTER' and status in ('ING', 'NEW')
            ''')
            qty = cursor.fetchone()[0]
            print('qty: ' + str(qty))
            if qty == 0:
                cursor.execute('''
                    update t_ybc_account r join (select nickname from t_ybc_account where type = 3) s
                    on r.promotionCode = s.nickname
                        set type = 4
                    where
                        r.type is null
                ''')
                cursor.execute('''
                    select '15889505097' from t_ybc_account where idStatus = 'success' and type = 1 order by rand() limit 1
                ''')
                mobile = cursor.fetchone()[0]
                print('mobile', mobile)
                cursor.execute('''
                    select mobile, nickname, type from t_ybc_account where mobile = %s and idStatus = 'success'
                    union
                    select mobile, nickname, type from t_ybc_account where promotionCode in (
                        select nickname from t_ybc_account where mobile = %s
                    ) and idStatus = 'success'
                    union
                    select mobile, nickname, type from t_ybc_account where promotionCode in (
                        select nickname from t_ybc_account where promotionCode = (
                            select nickname from t_ybc_account where mobile = %s
                        )
                    ) and idStatus = 'success'
                ''', (mobile, mobile, mobile))
                users = cursor.fetchall()
                for user in users:
                    mobile = user[0]
                    nickname = user[1]
                    user_type = int(user[2])
                    cursor.execute('''
                        select count(1) from t_ybc_account where promotionCode = %s
                    ''', (nickname))
                    count = cursor.fetchone()[0]
                    if user_type == 1 and count < 3:
                        print(user_type, 'need ', 3 - count)
                        new_register(nickname, cursor)
                    elif user_type == 2 and count < 3:
                        print(user_type, 'need ', 3 - count)
                        new_register(nickname, cursor)
                    elif user_type == 3 and count < 30:
                        print(user_type, 'need ', 30 - count)
                        new_register(nickname, cursor)
            connection.commit()
            time.sleep(5)
finally:
    connection.close()
